/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLType;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;

/**
 * <p>Project: jdemo202203 - CallDemo
 * <p>Powered by webrx On 2022-11-01 11:18:16
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class CallDemo {
    Connection conn = null;

    @Test @DisplayName("存储过程调用")
    void call(){
        try{
            //CallableStatement cst = conn.prepareCall("call p_dbs");
            //CallableStatement cst = conn.prepareCall("call p_dbs()");
            //CallableStatement cst = conn.prepareCall("{call p_dbs()}");
            //ResultSet rs = cst.executeQuery();
            //while(rs.next()){
            //    System.out.println(rs.getString(1));
            //}

            //jdbc 调用执行有in参数的存储过程
            //CallableStatement cst = conn.prepareCall("{call stuadd(?,?)}");
            //cst.setString(1,"王强");
            //cst.setInt(2,58);
            //int rows = cst.executeUpdate();
            //System.out.println(rows);

            //jdbc 调用执行有out参数的存储过程
            CallableStatement cst = conn.prepareCall("{call stucount(?)}");
            cst.registerOutParameter(1, Types.INTEGER);
            System.out.println(cst.execute());

            int count = cst.getInt(1);
            System.out.println(count);


        }catch(Exception e){
            e.printStackTrace();
        }
    }


    @Test
    void version(){
        try{
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select version(),now()");
            rs.next();
            System.out.println(rs.getString(1));
            System.out.println(rs.getString(2));
        }catch(Exception e){
            e.printStackTrace();
        }

    }


    @BeforeEach
    void init() throws IOException {
        //加载src/main/resources/db.propertie文件配置 信息
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        prop.load(is);
        String driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
        String url = prop.getProperty("db.url", "jdbc:mysql:/");
        String user = prop.getProperty("db.username", "root");
        String pwd = prop.getProperty("db.password", "");
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
